package com.liangtian.mdm.system.user.util;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

public class ExcelImport {

    /**
     * Excel 2003
     */
    private final static String XLS = "xls";
    /**
     * Excel 2007
     */
    private final static String XLSX = "xlsx";

    private Log logger = LogFactory.getLog(super.getClass());


    public static Workbook getWorkbook(File path) throws InvalidFormatException, IOException {
        FileInputStream fis = new FileInputStream(path);
        Workbook workbook = WorkbookFactory.create(fis);
        if (fis != null) {
            fis.close();
        }
        return workbook;
    }


    /**
     * 读取某个工作簿上的所有单元格的值。
     * July 30, 2013 3:27:08 PM
     *
     * @param sheetOrder 工作簿序号，从0开始。
     * @return List<Object [ ]> 所有单元格的值。
     * @throws IOException 加载excel文件IO异常。
     */
    public static List<Object[]> read(Sheet sheet) throws FileNotFoundException, IOException, InvalidFormatException {
        //用来记录excel值
        List<Object[]> valueList = new LinkedList<Object[]>();
        //循环遍历每一行、每一列。
        for (Row row : sheet) {
            //每一行
            Object[] rowObject = null;
            Row headRow = sheet.getRow(0);
            for (int j = 0; j < headRow.getLastCellNum(); j++) {
                Cell cell = row.getCell((short) j);
                if (cell == null) {
                    rowObject = CollectionUtil.addObjectToArray(rowObject, " ");
                    continue;
                }
                String data = null;
                //cell.getCellType是获得cell里面保存的值的type  
                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_BOOLEAN:
                        //得到Boolean对象的方法 
                        Boolean boolValue = cell.getBooleanCellValue();
                        if (String.valueOf(boolValue) == null) {
                            data = " ";
                        } else {
                            data = String.valueOf(boolValue);
                        }
                        rowObject = CollectionUtil.addObjectToArray(rowObject, data);
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        //先看是否是日期格式  
                        if (DateUtil.isCellDateFormatted(cell)) {
                            Date dateCell = cell.getDateCellValue();
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                            data = sdf.format(dateCell);
                            //读取日期格式  
                            rowObject = CollectionUtil.addObjectToArray(rowObject, data);
                        } else {
                            DecimalFormat df = new DecimalFormat();
                            //单元格的值,替换掉,
                            String value = df.format(cell.getNumericCellValue()).replace(",", "");
                            if (String.valueOf(value) == null) {
                                data = " ";
                            } else {
                                data = String.valueOf(value);
                            }
                            //读取数字  
                            rowObject = CollectionUtil.addObjectToArray(rowObject, data);
                        }
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        //读取公式  
                        rowObject = CollectionUtil.addObjectToArray(rowObject, cell.getCellFormula());
                        break;
                    case Cell.CELL_TYPE_STRING:
                        //读取String 
                        data = cell.getRichStringCellValue().toString();
                        if (data == null) {
                            data = " ";
                        }
                        rowObject = CollectionUtil.addObjectToArray(rowObject, data);
                        break;
                    case Cell.CELL_TYPE_BLANK: // 空值
                        data = " ";
                        rowObject = CollectionUtil.addObjectToArray(rowObject, data);
                        break;
                    default:
                        System.out.print("\n---读取的Excel单元格格式不支持---");
                        break;
                }
            }
            //将这行添加到list。
            valueList.add(rowObject);
        }
        return valueList;
    }

}
